﻿using Comm;
using Kinlo.Model;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Kinlo.Repo.MySql
{
    public class AfterWeightFirstRep
    {
        string strConn;
        //Database DB;
        public AfterWeightFirstRep()
        { 
            strConn = System.Configuration.ConfigurationManager.ConnectionStrings["WeightDBMySql"].ConnectionString;
        }
         
        public List<AfterWeightFirst> GetAfterFirst(string time1, string time2, string code, string okngStatus)
        {
            try
            {
                if (string.IsNullOrEmpty(code))
                {
                    code = "%";
                }
                else
                {
                    code = "%" + code + "%";
                }

                using (var conn = new MySqlConnection(strConn))
                {
                    var cmd = string.Format(@"SELECT ProductDate ,`Index` ,Code ,TrayNum ,TrayNo ,TrayIndex ,TrayCol ,BeforWeight_One ,AfterWeight_One ,
                        InjectionNum_One, InjectionNum_Offset_One, ISSuppled_One, SupplementNum_One, SupplementWeight_One,BatchNo,
                               Result_One, RangeOfInjection_One, IsValidate FROM AfterWeight_First
                        WHERE ProductDate BETWEEN '{0}' AND '{1}' AND (Code LIKE '{2}' or Code is null)  AND Result_One LIKE '{3}' ORDER BY ProductDate DESC ",
                               time1, time2, code, okngStatus);
                    var sqlCmd = new MySqlCommand(cmd, conn);
                    conn.Open();
                    var reader = sqlCmd.ExecuteReader();

                    return reader.ReaderToList<AfterWeightFirst>();
                }
            }
            catch (Exception ex)
            {
                Logger.Log.ErrorFormat("{0} {1} {2}", "AfterWeightFirstRep", "GetAfterFirst", ex);
                return null;
            }
        }
        public int DeleteAfterWeight(AfterWeightFirst w)
        {
            try
            {
                using (var conn = new MySqlConnection(strConn))
                {
                    var cmd = string.Format(@"DELETE from AfterWeight_First WHERE Code='{0}'", w.Code);
                    var sqlCmd = new MySqlCommand(cmd, conn);
                    conn.Open();
                    return sqlCmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Logger.Log.ErrorFormat("{0} {1} {2}", "AfterWeightFirstRep", "DeleteAfterWeight", ex);
                return 0;
            }
        }

        public int Add2(AfterWeightFirst w)
        {
            try
            {
                using (var conn = new MySqlConnection(strConn))
                {
                    var cmd = @"INSERT AfterWeight_First
                                (ID, ProductDate ,Code ,TrayNum ,TrayNo ,TrayIndex ,TrayCol ,BeforWeight_One ,
                                  AfterWeight_One ,InjectionNum_One ,InjectionNum_Offset_One ,ISSuppled_One ,
                                  SupplementNum_One ,SupplementWeight_One ,Result_One ,RangeOfInjection_One,IsValidate,`Index`,BatchNo)
                        VALUES  ( @ID, @ProductDate ,@Code ,@TrayNum ,@TrayNo ,@TrayIndex ,@TrayCol ,@BeforWeight_One ,
                                  @AfterWeight_One ,@InjectionNum_One ,@InjectionNum_Offset_One ,@ISSuppled_One ,
                                  @SupplementNum_One ,@SupplementWeight_One ,@Result_One ,@RangeOfInjection_One,@IsValidate,@Index,@BatchNo)";

                    var sqlCmd = new MySqlCommand(cmd, conn);
                    sqlCmd.Parameters.Add(new MySqlParameter("@ID", w.ID == null ? DBNull.Value : (object)w.ID));
                    sqlCmd.Parameters.Add(new MySqlParameter("@ProductDate", w.ProductDate == null ? DBNull.Value : (object)w.ProductDate));
                    sqlCmd.Parameters.Add(new MySqlParameter("@Code", w.Code == null ? DBNull.Value : (object)w.Code));
                    sqlCmd.Parameters.Add(new MySqlParameter("@TrayNum", w.TrayNum == null ? DBNull.Value : (object)w.TrayNum));
                    sqlCmd.Parameters.Add(new MySqlParameter("@TrayNo", w.TrayNo == null ? DBNull.Value : (object)w.TrayNo));
                    sqlCmd.Parameters.Add(new MySqlParameter("@TrayIndex", w.TrayIndex == null ? DBNull.Value : (object)w.TrayIndex));
                    sqlCmd.Parameters.Add(new MySqlParameter("@TrayCol", w.TrayCol == null ? DBNull.Value : (object)w.TrayCol));
                    sqlCmd.Parameters.Add(new MySqlParameter("@BeforWeight_One", w.BeforWeight_One == null ? DBNull.Value : (object)w.BeforWeight_One));
                    sqlCmd.Parameters.Add(new MySqlParameter("@AfterWeight_One", w.AfterWeight_One == null ? DBNull.Value : (object)w.AfterWeight_One));
                    sqlCmd.Parameters.Add(new MySqlParameter("@InjectionNum_One", w.InjectionNum_One == null ? DBNull.Value : (object)w.InjectionNum_One));
                    sqlCmd.Parameters.Add(new MySqlParameter("@InjectionNum_Offset_One", w.InjectionNum_Offset_One == null ? DBNull.Value : (object)w.InjectionNum_Offset_One));
                    sqlCmd.Parameters.Add(new MySqlParameter("@ISSuppled_One", w.ISSuppled_One == null ? DBNull.Value : (object)w.ISSuppled_One));
                    sqlCmd.Parameters.Add(new MySqlParameter("@SupplementNum_One", w.SupplementNum_One == null ? DBNull.Value : (object)w.SupplementNum_One));
                    sqlCmd.Parameters.Add(new MySqlParameter("@SupplementWeight_One", w.SupplementWeight_One == null ? DBNull.Value : (object)w.SupplementWeight_One));
                    sqlCmd.Parameters.Add(new MySqlParameter("@Result_One", w.Result_One == null ? DBNull.Value : (object)w.Result_One));
                    sqlCmd.Parameters.Add(new MySqlParameter("@RangeOfInjection_One", w.RangeOfInjection_One == null ? DBNull.Value : (object)w.RangeOfInjection_One));
                    sqlCmd.Parameters.Add(new MySqlParameter("@IsValidate", w.IsValidate == null ? DBNull.Value : (object)w.IsValidate));
                    sqlCmd.Parameters.Add(new MySqlParameter("@Index", w.Index == null ? DBNull.Value : (object)w.Index));
                    sqlCmd.Parameters.Add(new MySqlParameter("@BatchNo", w.BatchNo == null ? DBNull.Value : (object)w.BatchNo));
                     
                    conn.Open();
                    return sqlCmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Logger.Log.ErrorFormat("{0} {1} {2}", "AfterWeightFirstRep", "Add2", ex);
                return 0;
            }



        }


        public IList<AfterWeightFirst> GetAfterFirst1()
        {
            try
            {
                using (var conn = new MySqlConnection(strConn))
                {
                    var cmd = @"SELECT  * FROM AfterWeight_First WHERE IsValidate=1 AND	ProductDate > DATE_ADD(NOW(),INTERVAL -7 DAY) ORDER BY ProductDate DESC LIMIT 1000";
                    var sqlCmd = new MySqlCommand(cmd, conn);
                    conn.Open();
                    var AfterWeightFirsts = sqlCmd.ExecuteReader().ReaderToList<AfterWeightFirst>();
                    if (AfterWeightFirsts.Count > 0)
                        return AfterWeightFirsts;
                }
                return null;
            }
            catch (Exception ex)
            {
                Logger.Log.ErrorFormat("{0} {1} {2}", "AfterWeightFirstRep", "GetAfterFirst1", ex);
                return null;
            }
        }

        public DataTable GetDataBase()
        {
            try
            {
                using (var conn = new MySqlConnection(strConn))
                {
                    var cmd = @"SELECT REPLACE(CONVERT(NVARCHAR(19),T.ProductDate,121),'-','/') AS ProductDate  ,
                   T.[1] ,T.[2] ,T.[3] ,T.[4] ,T.[5] ,T.[6] ,T.[7] ,T.[8] ,T.[9] , T.[10] , T.[11] , T.[12]
                    from dbo.AfterWeight_First as C 
                    PIVOT(MIN(InjectionNum_One) FOR [TrayCol] IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS T";
                    MySqlDataAdapter da = new MySqlDataAdapter(cmd, conn);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    return dt;
                }
            }
            catch (Exception ex)
            {
                Logger.Log.ErrorFormat("{0} {1} {2}", "AfterWeightFirstRep", "GetDataBase", ex);
                return null;
            }
        }

        public List<UnExportDate> GetUnExportDates()
        {
            try
            {
                using (var conn = new MySqlConnection(strConn))
                {
                    var cmd = @"SELECT DISTINCT CONVERT(ProductDate,DATE)  as Date FROM AfterWeight_First WHERE (IsExport=0 OR IsExport IS NULL) AND ( ProductDate< CONVERT(NOW(),DATE)) ORDER BY Date ";
                    var sqlCmd = new MySqlCommand(cmd, conn);
                    conn.Open();
                    var unExports = sqlCmd.ExecuteReader().ReaderToList<UnExportDate>();
                    if (unExports.Count > 0)
                        return unExports;
                }
                return null;
            }
            catch (Exception ex)
            {
                Logger.Log.ErrorFormat("{0} {1} {2}", "AfterWeightFirstRep", "GetUnExportDates", ex);
                return null;
            }
        }

        public List<AfterWeightFirst> GetAfterFirst(string date)
        {
            try
            {
                using (var conn = new MySqlConnection(strConn))
                {
                    var cmd = string.Format(@"SELECT * FROM AfterWeight_First WHERE  CONVERT(ProductDate,DATE)='{0}'", date);
                    var sqlCmd = new MySqlCommand(cmd, conn);
                    conn.Open();
                    var reader = sqlCmd.ExecuteReader();
                    return reader.ReaderToList<AfterWeightFirst>();
                }
            }
            catch (Exception ex)
            {
                Logger.Log.ErrorFormat("{0} {1} {2}", "AfterWeightFirstRep", "GetAfterFirst", ex);
                return null;
            }
        }

        public List<AfterWeightFirst> GetAfterFirstByTime(DateTime date1, DateTime date2)
        {
            try
            {
                using (var conn = new MySqlConnection(strConn))
                {
                    var cmd = string.Format(@" SELECT * FROM AfterWeight_First WHERE (IsExport=0 or IsExport is NULL) AND ProductDate BETWEEN '{0}' and '{1}'", 
                        date1.ToString("yyyy-MM-dd HH:mm:ss"),date2.ToString("yyyy-MM-dd HH:mm:ss"));
                    var sqlCmd = new MySqlCommand(cmd, conn);
                    conn.Open();
                    var reader = sqlCmd.ExecuteReader();
                    return reader.ReaderToList<AfterWeightFirst>();
                }
            }
            catch (Exception ex)
            {
                Logger.Log.ErrorFormat("{0} {1} {2}", "AfterWeightFirstRep", "GetAfterFirst", ex);
                return null;
            }
        }

        public int UpdateExportDataStatus(string date)
        {
            try
            {
                using (var conn = new MySqlConnection(strConn))
                {
                    var cmd = string.Format(@"UPDATE  AfterWeight_First SET IsExport=1 WHERE  CONVERT(ProductDate,DATE)='{0}'", date);
                    var sqlCmd = new MySqlCommand(cmd, conn);
                    conn.Open();
                    return sqlCmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Logger.Log.ErrorFormat("{0} {1} {2}", "AfterWeightFirstRep", "UpdateExportDataStatus", ex);
                return 0;
            }
        }
        public int UpdateExportDataStatus(Guid? id)
        {
            try
            {
                using (var conn = new MySqlConnection(strConn))
                {
                    var cmd = string.Format(@"UPDATE  AfterWeight_First SET IsExport=1 WHERE ID='{0}'  ", id);
                    var sqlCmd = new MySqlCommand(cmd, conn);
                    conn.Open();
                    return sqlCmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Logger.Log.ErrorFormat("{0} {1} {2}", "AfterWeightFirstRep", "UpdateExportDataStatus", ex);
                return 0;
            }
        }
    }

}
